Generated code - QuerySpec, scalar query creation and fetching

Queries can be converted to scalar queries. This can be necessary when a query is guaranteed to return a single value and that value has to be used in e.g. a predicate. To convert a query to a scalar query, use any of the following methods

• Append the query with .CountRow() (or CountRowBig()). This will convert the query in a SELECT COUNT(*) FROM query construct. For EntityQuery<T> instances, it will simply replace the original entity projection with a COUNT(*). For DynamicQueries, it will wrap the query.
• Append the query with an Aggregate method The following are aggregate methods, which all require a parameter (field or expression)
  • Avg(parameter). Will produce the average of the parameter element over the query called on. Distinct variant: AvgDistinct
  • Count(parameter). Will produce the count of the parameter element over the query called on. Distinct variant: CountDistinct.
  • CountBig(parameter). Same as Count(parameter) but now uses COUNT_BIG(), a SQL Server specific aggregate function
  • Max(parameter). Will produce the max value of the parameter element over the query called on.
  • Min(parameter). As Max() but now will produce the min value of the parameter element.
  • StDev(parameter). Will produce the standard deviation of the parameter element over the query called on. Distinct variant: StDevDistinct()
  • Sum(parameter). Will produce the summation of the parameter element over the query called on. Distinct variant: SumDistinct()
  • Variance(parameter). Will produce the variance of the parameter element over the query called on. Distinct variant VarianceDistinct()
• Call .ToScalar() on the query. This will produce a scalar query with the first element in the projection as the field to return. An overload accepts a boolean whether to specify a row limit clause.

To enforce a row limit, the method .ForceRowLimit() can be called on a scalar query expression. Row limits are required if the scalar query can potentially return multiple elements and just 1 element is required.

Fetching Scalar queries

Fetching scalar queries is possbly by using a wrapping Select() call. Below are several examples of constructing and fetching scalar queries directly.

var qf = new QueryFactory();

// Scalar query which fetches a boolean using Any(). 
var q = qf.Create().Select(qf.Customer.Where(CustomerFields.CustomerId == "CHOPS").Any());
// Adapter
var exists = adapter.FetchScalar<bool>(q);
// SelfServicing
// var exists = new TypedListDAO().GetScalar<bool>(q, null);


// Another scalar query using Any() fetching a boolean.
var q = qf.Create()
	  .Select(qf.Customer.Where(CustomerFields.Country=="Germany")
  		    .Any(CustomerFields.CustomerId=="ALFKI"));
// Adapter
var exists = adapter.FetchScalar<bool>(q);
// SelfServicing
// var exists = new TypedListDAO().GetScalar<bool>(q, null);


// Fetching the total of all orders. 
var q = qf.Create()
	.Select(qf.Customer.As("C")
		.Select(CustomerFields.CustomerId,
			qf.Order
			    .CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
			    .Select(qf.OrderDetail
				   	.CorrelatedOver(OrderDetailEntity.Relations.OrderEntityUsingOrderId)
					.Select(OrderDetailFields.Quantity * OrderDetailFields.UnitPrice)
					.Sum().As("OrderTotal"))
			    .Sum().As("TotalAllOrders"))
			.Max());
// Adapter.
var result = adapter.FetchScalar<decimal>(q);
// SelfServicing
// var result = new TypedListDAO().GetScalar<decimal>(q, null);


// Fetching the rowcount of a query defined separately. 
var q = qf.Create()
	.Select(CustomerFields.CustomerId, CustomerFields.CompanyName, 
 	        OrderFields.OrderId, OrderFields.OrderDate)
	.From(qf.Customer.InnerJoin(CustomerEntity.Relations.OrderEntityUsingCustomerId));
// Adapter.
var count = adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
// SelfServicing
// var count = new TypedListDAO().GetScalar<int>(qf.Create().Select(q.CountRow()), null);